# @Project: diablo
# @File: merchant_stock_related
# @Time: 2023/12/20 19:02
# @User: Jolly
# @Email: jolly@007vin.com

"""
指定公司库存关联
"""

import time
import threading

from pymongo import MongoClient
from pymongo.errors import ConnectionFailure
import pymysql

from dbutils.pooled_db import PooledDB

lock = threading.Lock()


class MerchantStockRelated:

    def __init__(self):

        start_time = time.time()

        # 宏德分公司（除了总仓和一线）
        self.hongde_group = {"3": "宏德",
                             "1390f84351fe464b90dcdbf3a164ff24": "宏德龙华总仓-一线品牌",
                             "fc2e72e26a03423c8f8ee8576debf2ec": "龙华鑫众奥",
                             "3fd50208a10c42ac87c6d84bd9f5661f": "龙华名捷",
                             "a62152e4fe1f49d186d23e060f272e65": "龙华名诚行",
                             "1b5044001e05415ab9f19afbeb73b9c7": "罗湖德众",
                             "3d80a9253b8640d4afc1da174a39d085": "罗湖名捷",
                             "230afea849fd489d88aa6dfeab26a3b3": "罗湖名诚行",
                             "8f5992a1d102410e95cd76f70f0cec30": "龙岗德众",
                             "c217b4952279478889397f15d9968735": "龙岗名捷",
                             "84c2ec02335b4f7f8cabcbbda0d133af": "龙岗名诚行",
                             "411e70375f484ae0b7948b326401a205": "平湖鑫德众",
                             "b8b351a60d114d3a8a078ad178eb41c5": "平湖名捷",
                             "f73a74ddc6584457bd573b400e217110": "平湖名诚行",
                             "0e402537190b4677b5d6d8a1c8af0b2f": "寮步鑫众奥",
                             "c7823ee69b7b4ed4ae8ddab3ee0c12c7": "寮步路捷",
                             "92560c123ce347b9806b6756a0d54097": "寮步名诚行",
                             "79f7412227fb4928bb12f2dc11048a11": "沙井鑫众奥",
                             "4b2c95dd10654036909423de19777d5d": "沙井名捷",
                             "920980642c63490abfad211727bbee7d": "沙井名诚行",
                             "e44f3831dede4640b9bdd40eeaabdc26": "宝安鑫众奥",
                             "50ff6b30688d40b48879857847dcc320": "宝安名捷",
                             "192b5c4f311442cbb613ed63a07d8f78": "宝安名诚行",
                             "c982247f26b74166884d0ca2087d5c17": "厚街鑫众奥",
                             "acf15daf375f497390ac274538e3e058": "厚街名捷",
                             "a2f1c6009ca44cbf8b8e6ddb685b5eae": "厚街名诚行",
                             "3546da2f191b4d1cb8979dad8e42593f": "塘厦鑫众奥",
                             "203ffd0f7d3645f4ba59387b6b532308": "塘厦名捷",
                             "cd5ce52f2db34e39bc66e88bc41238b0": "塘厦名诚行"}

        # 初始化数据库连接
        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 初始化数据库连接
        self.mysql_conn = self.get_mysql_connection()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=150,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def get_mysql_connection(self):
        maxconnections = 100  # 最大连接数
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.21',
            user='users',
            port=3306,
            passwd='uSR@ll7GnJDopei',
            db='users',
            use_unicode=True)

        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.47',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@#H^&JD',
        #     db='users',
        #     use_unicode=True)

        return pool

    def check_related(self, merchant_id, bind_merchant_id):
        """
        查看关联是否存在
        """
        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select * from diablo_merchant_cst_related d where d.merchant_id='{merchant_id}' and d.bind_merchant_id='{bind_merchant_id}' """

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:
                return True
            else:
                return False

        except Exception as e:
            print("获取关联存在失败 - mysql：", e)
        finally:
            mysql_conn.close()

    def insert_data(self):
        """
        插入信息
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            for merchant_id, merchant_name in self.hongde_group.items():

                for bind_merchant_id, bind_merchant_name in self.hongde_group.items():
                    # 如果存在关联记录则不写入

                    check = self.check_related(merchant_id, bind_merchant_id)

                    if check:
                        continue

                    key = ['merchant_id', 'merchant_name', 'bind_merchant_id', 'bind_merchant_name',
                           'sort', 'status', 'is_main']

                    key_param = [merchant_id, merchant_name, bind_merchant_id, bind_merchant_name, 1, 1, 0]

                    value = ""

                    for i, v in enumerate(key):
                        value += '"{}",'.format(key_param[i])

                    values = ", ".join('{0}=values({0})'.format(k) for k in key)

                    try:
                        sql = '''insert into diablo_merchant_cst_related ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                            ",".join(key), value[:-1], values)
                        # print(sql)
                        cursor.execute(sql)
                        mysql_conn.commit()
                    except Exception as e:
                        pass

        except Exception as e:
            print("写入公司关联信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return {}


if __name__ == "__main__":
    service = MerchantStockRelated()
    service.insert_data()